package com.dao.impl;

import com.bean.Customer;
import com.bean.Goods;
import com.dao.StatisticsDao;
import com.util.JDBC_Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StatisticsDaoImpl implements StatisticsDao {

    Connection conn = null;
    ResultSet rs= null;
    PreparedStatement ps = null;


    @Override
    public List<Goods> getSellStatisticsList() {
        List<Goods> list = new ArrayList<>();
        conn = JDBC_Util.getConnection();
        String sql =  "select tg.manufactor,tg.type,sum(number) from t_sell_info" +
                " inner join t_goods tg on t_sell_info.goods_id = tg.id" +
                " inner join t_sell ts on t_sell_info.sell_id = ts.id" +
                " group by tg.manufactor,tg.type";
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                String manufactor = rs.getString("manufactor");
                String type = rs.getString("type");
                int sum = rs.getInt("sum(number)");

                Goods goods = new Goods();
                goods.setType(type);
                goods.setManufactor(manufactor);
                goods.setSellNumber(sum);

                list.add(goods);
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }

        return list;
    }

    @Override
    public List<Goods> findSellStatisticsList(String manufactor, String startDate, String endDate) {
        List<Goods> list = new ArrayList<>();
        conn = JDBC_Util.getConnection();

        String sql = "select tg.manufactor,tg.type,sum(number) from t_sell_info" +
                " inner join t_goods tg on t_sell_info.goods_id = tg.id" +
                " inner join t_sell ts on t_sell_info.sell_id = ts.id" +
                " where 1 = 1";

        if(!manufactor.equals("")  && manufactor != null){
            sql += " and tg.manufactor like '%"+manufactor+"%'";
        }

        if(!startDate.equals("")  && startDate != null){
            sql += " and ts.sell_date > '"+startDate+"'";
        }

        if(!endDate.equals("")  && endDate != null){
            sql += " and ts.sell_date < '"+endDate+"'";
        }

        sql += " group by tg.manufactor,tg.type";

        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                String newManufactor = rs.getString("manufactor");
                String type = rs.getString("type");
                int sum = rs.getInt("sum(number)");

                Goods goods = new Goods();
                goods.setType(type);
                goods.setManufactor(newManufactor);
                goods.setSellNumber(sum);

                list.add(goods);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return list;
    }

    @Override
    public List<Customer> getCustomerStatisticsList() {
        List<Customer> list = new ArrayList<>();
        conn = JDBC_Util.getConnection();
        String sql =  "select tc.customer_name,sum(t_sell.money) from t_sell " +
                " inner join t_customer tc on t_sell.customer_id = tc.id" +
                " group by customer_name";
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                String customerName = rs.getString("customer_name");
                int allMoney = rs.getInt("sum(t_sell.money)");

                Customer customer = new Customer();
                customer.setName(customerName);
                customer.setAllMoney(allMoney);

                list.add(customer);
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }

        return list;
    }

    @Override
    public List<Customer> findCustomerStatisticsList(String customerName, String startDate, String endDate) {
        List<Customer> list = new ArrayList<>();
        conn = JDBC_Util.getConnection();

        String sql = "select tc.customer_name,sum(t_sell.money) from t_sell " +
                " inner join t_customer tc on t_sell.customer_id = tc.id" +
                " where 1 = 1 ";

        if(!customerName.equals("")  && customerName != null){
            sql += " and tc.customer_name like '%"+customerName+"%'";
        }

        if(!startDate.equals("")  && startDate != null){
            sql += " and sell_date > '"+startDate+"'";
        }

        if(!endDate.equals("")  && endDate != null){
            sql += " and sell_date < '"+endDate+"'";
        }

        sql += " group by customer_name";

        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                String newCustomerName = rs.getString("customer_name");
                int allMoney = rs.getInt("sum(t_sell.money)");

                Customer customer = new Customer();
                customer.setName(newCustomerName);
                customer.setAllMoney(allMoney);

                list.add(customer);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }

        return list;
    }
}
